//
//  YWReportTroubleDBManager.m
//  maintain
//
//  Created by 烟雨云渊 on 17/3/2.
//  Copyright © 2017年 ewin. All rights reserved.
//

#import "YWReportTroubleDBManager.h"
#import "YWDBTool.h"
#import "YWMissionManager.h"
#import "YWMission.h"
#import "YWTroubleMissionDBManager.h"
#import "YWTroubleMissionModel.h"

#import "YWReportTroubleModel.h"
#import "YWEquipmentType.h"
#import "YWLocation.h"
#import "YWFloor.h"
#import "YWApartment.h"
#import "YWBuilding.h"
#import "YWHandler.h"
#import "YWEquipment.h"
#import "YWCirculationInfo.h"

#import "YWCirculationManager.h"
#import "YWMissionManager.h"
#import "YWLoginTool.h"
#import "YWLoginUser.h"
#import "YWCommonTool.h"
#import "YWEquipmentFieldRecord.h"
#import "YWEquipmentTroubleRecord.h"

@implementation YWReportTroubleDBManager
YWSingletonM(Manager)

- (FMDatabase*)fmdb{
    return [[YWDBTool shareDBTool] fmdb];
}
- (FMDatabaseQueue*)dbQeue{
    return [[YWDBTool shareDBTool] dbQeue];
}

- (void)createAllReportTroubleTable{
    [self createReportTroubleTable];
}

- (BOOL)addColumInReportTroubleTable{
    __block BOOL executeResult = YES;
    //增加systemTypeId字段
    if (![[self fmdb] columnExists:@"systemTypeId" inTableWithName:@"YW_ReportTrouble"]) {
        [[self fmdb] executeUpdate:@"alter table 'YW_ReportTrouble' add column systemTypeId integer;"];
    }
    return executeResult;
}

#pragma mark - 创建报障数据库表
- (BOOL)createReportTroubleTable{
    NSString * sql = @"CREATE TABLE IF NOT EXISTS 'YW_ReportTrouble' (troubleId integer,troubleSequence varchar,qrcodeId varchar,equipmentTypeId integer,locationId integer,floorId integer,apartmentId integer,buildingId varchar,reporterId integer,reportTime timestamp,note varchar,cancelNote varchar,troubleStatus integer,channel integer,equipmentId varchar,uniqueTag varchar,observerIds varchar,read boolean DEFAULT 0,uploadFail boolean DEFAULT 0,systemTypeId integer)";
    return [[self fmdb] executeUpdate:sql];
}

- (BOOL)updateReportTroubleWithArray:(NSArray*)reportTroubleArray{
    if (reportTroubleArray.count == 0) {
        return YES;
    }
    __block BOOL executeResult = YES;
    __block NSArray* blockReportTroubleArray = reportTroubleArray;
    __block NSMutableArray* troubleMissionArray = [NSMutableArray array];
//    __block NSMutableArray* equipmentFieldArray = [NSMutableArray array];
    __block NSMutableArray* circulationArray = [NSMutableArray array];
    __block NSMutableArray* troubleRecordArray = [NSMutableArray array];
    __block NSString* sql = nil;
    [self.dbQeue inTransaction:^(FMDatabase* db, BOOL* rollback) {
        [blockReportTroubleArray enumerateObjectsUsingBlock:^(YWReportTroubleModel*  _Nonnull reportTrouble, NSUInteger idx, BOOL * _Nonnull stop) {
            //更新自定义属性表(安卓的忽略)
//            [reportTrouble.equipmentFields enumerateObjectsUsingBlock:^(YWEquipmentFieldRecord* _Nonnull fieldRecord, NSUInteger idx, BOOL* _Nonnull stop) {
//                fieldRecord.maintenanceTypeId = YWMaintenanceTypeTrouble;
//                [equipmentFieldArray addObject:fieldRecord];
//            }];
        
            //更新报障图片
            [reportTrouble.pictures enumerateObjectsUsingBlock:^(YWPicture* _Nonnull picture, NSUInteger idx, BOOL* _Nonnull stop) {
                picture.missionId = reportTrouble.troubleId;
                picture.maintenanceType = YWMaintenanceTypeTrouble;
                
                sql = @"SELECT * FROM 'picture' WHERE pictureId=?;";
                FMResultSet* set = [db executeQuery:sql, picture.pictureId];
                if ([set next]) {
                    sql = @"UPDATE 'picture' SET pictureName =?,bigPictureUrl=?,smallPictureUrl =?,isLocalImage =?,uploadStatus=?,missionId=?,maintenanceType=? WHERE pictureId=?;";
                    executeResult = [db executeUpdate:sql, picture.pictureName, picture.bigPictureUrl, picture.smallPictureUrl, @(picture.isLocalImage), @(picture.uploadStatus), @(picture.missionId), @(picture.maintenanceType), picture.pictureId];
                }
                else {
                    sql = @"INSERT INTO 'picture' (pictureId,pictureName,bigPictureUrl,smallPictureUrl,isLocalImage,uploadStatus,missionId,maintenanceType,equipmentId,pictureTroubleType) VALUES (?, ?, ?, ?, ?, ?, ?,?,?,?);";
                    executeResult = [db executeUpdate:sql, picture.pictureId, picture.pictureName, picture.bigPictureUrl, picture.smallPictureUrl, @(picture.isLocalImage), @(picture.uploadStatus), @(picture.missionId), @(picture.maintenanceType), picture.equipmentId, @(picture.pictureTroubleType)];
                }
                if (!executeResult) {
                    *stop = YES;
                    *rollback = YES;
                };
            }];
            
            //更新流转信息
            if (reportTrouble.circulationInfos) {
                [circulationArray addObjectsFromArray:reportTrouble.circulationInfos];
            }
            //更新维修记录
            if (reportTrouble.records) {
                [reportTrouble.records enumerateObjectsUsingBlock:^(YWEquipmentTroubleRecordJson*  _Nonnull troubleRrcordJson, NSUInteger idx, BOOL * _Nonnull stop) {
                    troubleRrcordJson.missionId = reportTrouble.troubleId;
                    troubleRrcordJson.troubleId = reportTrouble.troubleId;
                }];
                [troubleRecordArray addObjectsFromArray:reportTrouble.records];
            }
            
            //更新维修单
            if (reportTrouble.troubleMission) {
                [troubleMissionArray addObject:reportTrouble.troubleMission];
            }
            //若先前有维修单，同步维修单状态(当另一账号退回，撤回任务时用到)
            if (reportTrouble.troubleId) {
                sql = @"SELECT * FROM 'YW_TroubleMission' WHERE troubleId=?;";
                FMResultSet* set = [db executeQuery:sql, @(reportTrouble.troubleId)];
                if ([set next]) {
                    sql = @"UPDATE 'YW_TroubleMission' SET troubleStatus=? WHERE troubleId=?;";
                    executeResult = [db executeUpdate:sql, @(reportTrouble.troubleStatus), @(reportTrouble.troubleId)];
                }
            }
            sql = @"SELECT * FROM 'YW_ReportTrouble' WHERE troubleId=?;";
            FMResultSet* set = [db executeQuery:sql, @(reportTrouble.troubleId)];
            if ([set next]) {
                sql = @"UPDATE 'YW_ReportTrouble' SET troubleSequence =?,qrcodeId =?,equipmentTypeId =?,locationId =?,floorId =?,apartmentId =?,buildingId =?,reporterId =?, reportTime=?,note =?,cancelNote =?,troubleStatus =?,channel =?,equipmentId =?,uniqueTag =?,observerIds=?,systemTypeId=? WHERE troubleId =?;";
                executeResult = [db executeUpdate:sql,reportTrouble.troubleSequence,reportTrouble.qrcodeId,@(reportTrouble.equipmentType.equipmentTypeId),@(reportTrouble.location.locationId),@(reportTrouble.floor.floorId),@(reportTrouble.apartment.apartmentId),reportTrouble.building.buildingId,@(reportTrouble.reporter.uniqueId),@(reportTrouble.reportTime),reportTrouble.note,reportTrouble.cancelNote,@(reportTrouble.troubleStatus),@(reportTrouble.channel),reportTrouble.equipment.equipmentId,reportTrouble.uniqueTag,reportTrouble.observerIds,@(reportTrouble.systemTypeId),@(reportTrouble.troubleId)];
            }
            else {
                sql = @"INSERT INTO 'YW_ReportTrouble' (troubleId,troubleSequence,qrcodeId,equipmentTypeId,locationId,floorId,apartmentId,buildingId,reporterId,reportTime,note,cancelNote,troubleStatus,channel,equipmentId,uniqueTag,observerIds,systemTypeId) VALUES (?, ?, ?, ?, ?, ?, ?,?, ?, ?, ?, ?, ?, ?, ?,?,?,?);";
                executeResult = [db executeUpdate:sql,@(reportTrouble.troubleId),reportTrouble.troubleSequence,reportTrouble.qrcodeId,@(reportTrouble.equipmentType.equipmentTypeId),@(reportTrouble.location.locationId),@(reportTrouble.floor.floorId),@(reportTrouble.apartment.apartmentId),reportTrouble.building.buildingId,@(reportTrouble.reporter.uniqueId),@(reportTrouble.reportTime),reportTrouble.note,reportTrouble.cancelNote,@(reportTrouble.troubleStatus),@(reportTrouble.channel),reportTrouble.equipment.equipmentId,reportTrouble.uniqueTag,reportTrouble.observerIds,@(reportTrouble.systemTypeId)];
            }
            if (!executeResult) {
                *stop = YES;
                *rollback = YES;
            };
            
            //如果是微信报障，更新报障人
            if (reportTrouble.troubleContact) {
                sql = @"SELECT * FROM 'weChatPostTroubleReporter' WHERE troubleId=?;";
                set = [db executeQuery:sql, @(reportTrouble.troubleId)];
                if ([set next]) {
                    sql = @"UPDATE 'weChatPostTroubleReporter' SET contactName =?,contactNo=? WHERE troubleId=?;";
                    executeResult = [db executeUpdate:sql, reportTrouble.troubleContact.contactName,reportTrouble.troubleContact.contactNo,@(reportTrouble.troubleId)];
                }else{
                    sql = @"INSERT INTO 'weChatPostTroubleReporter' (troubleId,contactName,contactNo) VALUES (?, ?, ?);";
                    executeResult = [db executeUpdate:sql, @(reportTrouble.troubleId), reportTrouble.troubleContact.contactName,reportTrouble.troubleContact.contactNo];
                }
            }
            //保存录入报障时候新建的位置
            if (reportTrouble.equipment == nil && reportTrouble.building && reportTrouble.apartment && reportTrouble.floor && reportTrouble.location) {
                YWBuilding *building = reportTrouble.building;
                YWApartment *apartment = reportTrouble.apartment;
                YWFloor *floor = reportTrouble.floor;
                YWLocation *location = reportTrouble.location;
                //楼盘
                sql = @"SELECT * FROM 'building' WHERE buildingId=?;";
                FMResultSet* set = [db executeQuery:sql, building.buildingId];
                if ([set next]) {
                    sql = @"UPDATE 'building' SET buildingName=? WHERE buildingId=?;";
                    executeResult = [db executeUpdate:sql, building.buildingName, building.buildingId];
                }
                //不保存楼盘，防止未授权楼盘
//                else {
//                    sql = @"INSERT INTO 'building' (buildingId,buildingName) VALUES (?,?);";
//                    executeResult = [db executeUpdate:sql, building.buildingId, building.buildingName];
//                }
                if (!executeResult) {
                    *rollback = YES;
                };
                //楼栋
                sql = @"SELECT * FROM 'apartment' WHERE apartmentId=?;";
                set = [db executeQuery:sql, @(apartment.apartmentId)];
                if ([set next]) {
                    sql = @"UPDATE 'apartment' SET apartmentName=?,buildingId=? WHERE apartmentId=?;";
                    executeResult = [db executeUpdate:sql, apartment.apartmentName, building.buildingId, @(apartment.apartmentId)];
                }
                else {
                    sql = @"INSERT INTO 'apartment' (apartmentId,apartmentName,buildingId) VALUES (?,?,?);";
                    executeResult = [db executeUpdate:sql, @(apartment.apartmentId), apartment.apartmentName, building.buildingId];
                }
                if (!executeResult) {
                    *rollback = YES;
                };
                //楼层
                sql = @"SELECT * FROM 'floor' WHERE floorId=?;";
                set = [db executeQuery:sql, @(floor.floorId)];
                if ([set next]) {
                    sql = @"UPDATE 'floor' SET floorName=?,apartmentId=? WHERE floorId=?;";
                    executeResult = [db executeUpdate:sql, floor.floorName, @(apartment.apartmentId), @(floor.floorId)];
                }else {
                    sql = @"INSERT INTO 'floor' (floorId,floorName,apartmentId) VALUES (?,?,?);";
                    executeResult = [db executeUpdate:sql, @(floor.floorId), floor.floorName, @(apartment.apartmentId)];
                }
                if (!executeResult) {
                    *rollback = YES;
                };
                //位置
                sql = @"SELECT * FROM 'location' WHERE locationId=?;";
                set = [db executeQuery:sql, @(location.locationId)];
                if ([set next]) {
                    sql = @"UPDATE 'location' SET locationName=?,floorId=? WHERE locationId=?;";
                    executeResult = [db executeUpdate:sql, location.locationName, @(floor.floorId), @(location.locationId)];
                }else {
                    sql = @"INSERT INTO 'location' (locationId, locationName , floorId ) VALUES (?,?,?);";
                    executeResult = [db executeUpdate:sql, @(location.locationId), location.locationName, @(floor.floorId)];
                }
                if (!executeResult) {
                    *rollback = YES;
                };
            }
            
            if (!executeResult) {
                *rollback = YES;
            };
            
        }];
        [db closeOpenResultSets];
    }];
//    [[YWMissionManager shareManager] updateEquipmentFieldRecords:equipmentFieldArray];
    [[YWCirculationManager shareManager] updateCirculationInfoWithArray:circulationArray];
    [[YWDBTool shareDBTool] saveEquipmentTroubleRecordWithJsonArray:troubleRecordArray];
    [[YWTroubleMissionDBManager shareManager] updateTroubleMissionWithArray:troubleMissionArray needUpdateReportTrouble:NO];
    return executeResult;
}

-(BOOL)updateReportTroubleStatus:(NSInteger)status troubleId:(NSInteger)troubleId{
    __block BOOL executeResult = YES;
    [self.dbQeue inDatabase:^(FMDatabase* db) {
        NSString* sql = @"UPDATE 'YW_ReportTrouble' SET troubleStatus=? WHERE troubleId=?;";
        executeResult = [db executeUpdate:sql, @(status), @(troubleId)];
        [db closeOpenResultSets];
    }];
    return executeResult;
}

- (BOOL)updateReportTroubleIdAndSequence:(YWReportTroubleModel*)troubleJson originTroubleId:(NSInteger)originTroubleId{
    __block BOOL executeResult = YES;
    [self.dbQeue inDatabase:^(FMDatabase* db) {
        NSString* sql = @"UPDATE 'YW_ReportTrouble' SET troubleId=?,troubleSequence=?,troubleStatus=?,uploadFail=0 WHERE troubleId=?;";
        executeResult = [db executeUpdate:sql, @(troubleJson.troubleId),troubleJson.troubleSequence,@(troubleJson.troubleStatus), @(originTroubleId)];
        [db closeOpenResultSets];
    }];
    return executeResult;
}

- (BOOL)setUploadFailWithReportTrouble:(YWReportTroubleModel*)reportTrouble{
    __block BOOL executeResult = YES;
    [self.dbQeue inDatabase:^(FMDatabase* db) {
        NSString* sql = nil;
        sql = @"UPDATE 'YW_ReportTrouble' SET uploadFail=? WHERE troubleId=?;";
        
        executeResult = [db executeUpdate:sql, @(reportTrouble.uploadFail), @(reportTrouble.troubleId)];
        [db closeOpenResultSets];
    }];
    return executeResult;
}

- (BOOL)deleteReportTroubleWithTroubleId:(NSInteger)troubleId orUniqueTag:(NSString *)uniqueTag{
    __block BOOL executeResult = YES;
    [self.dbQeue inDatabase:^(FMDatabase* db) {
        NSString* sql = nil;
        if (troubleId) {
            sql = @"select * from 'YW_ReportTrouble' where troubleId=?";
            FMResultSet *set = [db executeQuery:sql, @(troubleId)];
            if ([set next]) {
                sql = @"delete from 'YW_ReportTrouble' where troubleId=?";
                executeResult = [db executeUpdate:sql, @(troubleId)];
            }else{
                if (uniqueTag.length) {
                    sql = @"delete from 'YW_ReportTrouble' where uniqueTag=?";
                    executeResult = [db executeUpdate:sql, uniqueTag];
                }
            }
        }
        [db closeOpenResultSets];
    }];
    return executeResult;
}

- (NSArray *)getMyReportTroubleWithPage:(NSInteger)page{
    __block NSMutableArray* troubleArray = [NSMutableArray array];
    NSInteger showCount = page * 10;
    YWLoginUser *currentUser = [YWLoginTool loginUser];
    [self.dbQeue inDatabase:^(FMDatabase* db) {
        NSString* sql = nil;
        FMResultSet* set = nil;
        sql = [NSString stringWithFormat:@"SELECT * FROM 'YW_ReportTrouble' WHERE reporterId=%lu ORDER BY reportTime DESC LIMIT %lu",(long)currentUser.uniqueId,(long)showCount];
        set = [db executeQuery:sql];
        
        NSString *lastDate = nil;
        NSMutableArray *array = nil;
        while ([set next]) {
            YWReportTroubleModel* reportTrouble = [self reportTroubleWithFMResultSet:set FMDatabase:db needSelectTroubleMission:YES];
            NSString *timeDate = [YWCommonTool showChineseFormatterDate:reportTrouble.reportTime/1000];
            if (lastDate==nil || ![timeDate isEqualToString:lastDate]) {
                lastDate = timeDate;
                array = [NSMutableArray array];
            }
            [array addObject:reportTrouble];
            if (![troubleArray containsObject:array] && array.count) {
                [troubleArray addObject:array];
            }
        }
        [db closeOpenResultSets];
    }];
    return troubleArray.count ? troubleArray : nil;
}

- (NSArray*)getMyReportTroubleWithAssignStatus:(BOOL)isAssign page:(NSInteger)page{
    __block NSMutableArray* troubleArray = [NSMutableArray array];
    NSInteger showCount = page * 10;
    YWLoginUser *currentUser = [YWLoginTool loginUser];
    [self.dbQeue inDatabase:^(FMDatabase* db) {
        NSString* sql = nil;
        FMResultSet* set = nil;
        if (isAssign) {
            if (currentUser.isTroubleServer) {
                sql = [NSString stringWithFormat:@"SELECT * FROM 'YW_ReportTrouble' WHERE troubleId>0 AND (troubleStatus=? or troubleStatus=?) ORDER BY reportTime DESC LIMIT %ld",(long)showCount];//(creatorId=? or reporterId=?) and,creator 就是派单员 troubleStatus ASC,
                set = [db executeQuery:sql,@(YWTroubleMissionStatusAssign),@(YWTroubleMissionStatusFinish)];
            }else{
                
            }
        }else{
            if (currentUser.isTroubleServer) {
                sql = [NSString stringWithFormat:@"SELECT * FROM 'YW_ReportTrouble' WHERE troubleId>0 AND troubleStatus=%lu ORDER BY reportTime DESC LIMIT %ld",(unsigned long)YWTroubleMissionStatusUnAssign,(long)showCount];
                set = [db executeQuery:sql];
            }else{
                //                sql = [NSString stringWithFormat:@"SELECT * FROM 'YW_ReportTrouble' WHERE (reporterId=%lu or observerIds like '%%%lu%%') and troubleStatus=%lu ORDER BY troubleStatus ASC,reportTime DESC LIMIT %lu",(long)currentUser.uniqueId,(long)currentUser.uniqueId,(unsigned long)YWTroubleMissionStatusUnAssign,(long)showCount];
                //                set = [db executeQuery:sql];
            }
        }
        NSString *lastDate = nil;
        NSMutableArray *array = nil;
        while ([set next]) {
            YWReportTroubleModel* reportTrouble = [self reportTroubleWithFMResultSet:set FMDatabase:db needSelectTroubleMission:YES];
            NSString *timeDate = [YWCommonTool showChineseFormatterDate:reportTrouble.reportTime/1000];
            if (lastDate==nil || ![timeDate isEqualToString:lastDate]) {
                lastDate = timeDate;
                array = [NSMutableArray array];
            }
            [array addObject:reportTrouble];
            if (![troubleArray containsObject:array] && array.count) {
                [troubleArray addObject:array];
            }
        }
        [db closeOpenResultSets];
    }];
    return troubleArray.count ? troubleArray : nil;
}

- (NSArray *)getReportTroubleUnassignBillsWithPage:(NSInteger)page{
    __block NSMutableArray* troubleArray = [NSMutableArray array];
    NSInteger showCount = page * 10;
    [self.dbQeue inDatabase:^(FMDatabase* db) {
        NSString* sql = [NSString stringWithFormat:@"SELECT * FROM 'YW_ReportTrouble' WHERE troubleId>0 AND troubleStatus=%lu ORDER BY troubleStatus ASC,reportTime DESC LIMIT %ld",(unsigned long)YWTroubleMissionStatusUnAssign,(long)showCount];
        FMResultSet* set = [db executeQuery:sql];
        
        NSString *lastDate = nil;
        NSMutableArray *array = nil;
        while ([set next]) {
            YWReportTroubleModel* reportTrouble = [self reportTroubleWithFMResultSet:set FMDatabase:db needSelectTroubleMission:YES];
            NSString *timeDate = [YWCommonTool showChineseFormatterDate:reportTrouble.reportTime/1000];
            if (lastDate==nil || ![timeDate isEqualToString:lastDate]) {
                lastDate = timeDate;
                array = [NSMutableArray array];
            }
            [array addObject:reportTrouble];
            if (![troubleArray containsObject:array] && array.count) {
                [troubleArray addObject:array];
            }
        }
        [db closeOpenResultSets];
    }];
    return troubleArray.count ? troubleArray : nil;
}

- (NSArray *)getReportTroubleWithEquipmentId:(NSString*)equipmentId page:(NSInteger)page{
    __block NSMutableArray* troubleArray = [NSMutableArray array];
    NSInteger showCount = page * 10;
    [self.dbQeue inDatabase:^(FMDatabase* db) {
        NSString* sql = nil;
        FMResultSet* set = nil;
        sql = @"SELECT * FROM 'YW_ReportTrouble' WHERE equipmentId =? AND troubleId>0 ORDER BY reportTime DESC LIMIT ?";
        set = [db executeQuery:sql, equipmentId,@(showCount)];
        
        NSString *lastDate = nil;
        NSMutableArray *array = nil;
        while ([set next]) {
            YWReportTroubleModel* reportTrouble = [self reportTroubleWithFMResultSet:set FMDatabase:db needSelectTroubleMission:YES];
            NSString *timeDate = [YWCommonTool showChineseFormatterDate:reportTrouble.reportTime/1000];
            if (lastDate==nil || ![timeDate isEqualToString:lastDate]) {
                lastDate = timeDate;
                array = [NSMutableArray array];
            }
            [array addObject:reportTrouble];
            if (![troubleArray containsObject:array] && array.count) {
                [troubleArray addObject:array];
            }
        }
        [db closeOpenResultSets];
    }];
    return troubleArray.count ? troubleArray : nil;
}

- (NSArray *)getReportTroubleWithLocationId:(NSInteger)locationId page:(NSInteger)page{
    __block NSMutableArray* troubleArray = [NSMutableArray array];
    NSInteger showCount = page * 10;
    [self.dbQeue inDatabase:^(FMDatabase* db) {
        NSString* sql = nil;
        FMResultSet* set = nil;
        sql = @"SELECT * FROM 'YW_ReportTrouble' WHERE locationId =? AND troubleId>0  ORDER BY reportTime DESC LIMIT ?";//AND equipmentTypeId =0位置下显示设备的报障
        set = [db executeQuery:sql, @(locationId),@(showCount)];
        
        NSString *lastDate = nil;
        NSMutableArray *array = nil;
        while ([set next]) {
            YWReportTroubleModel* reportTrouble = [self reportTroubleWithFMResultSet:set FMDatabase:db needSelectTroubleMission:YES];
            NSString *timeDate = [YWCommonTool showChineseFormatterDate:reportTrouble.reportTime/1000];
            if (lastDate==nil || ![timeDate isEqualToString:lastDate]) {
                lastDate = timeDate;
                array = [NSMutableArray array];
            }
            [array addObject:reportTrouble];
            if (![troubleArray containsObject:array] && array.count) {
                [troubleArray addObject:array];
            }
        }
        [db closeOpenResultSets];
    }];
    return troubleArray.count ? troubleArray : nil;
}

- (YWReportTroubleModel*)getOneReportTroubleWithTroubleId:(NSInteger)troubleId{
    __block YWReportTroubleModel* reportTrouble = nil;
    [self.dbQeue inDatabase:^(FMDatabase* db) {
        NSString* sql = @"SELECT * FROM 'YW_ReportTrouble' WHERE troubleId = ?";
        FMResultSet* set = [db executeQuery:sql, @(troubleId)];
        if ([set next]) {
            reportTrouble = [self reportTroubleWithFMResultSet:set FMDatabase:db needSelectTroubleMission:YES];
        }
        [db closeOpenResultSets];
    }];
    return reportTrouble;
}

- (YWReportTroubleModel*)reportTroubleWithFMResultSet:(FMResultSet*)set FMDatabase:(FMDatabase*)db needSelectTroubleMission:(BOOL)need
{
    YWReportTroubleModel * reportTrouble = [[YWReportTroubleModel alloc] init];
    reportTrouble.troubleId = [set longForColumn:@"troubleId"];
    reportTrouble.uniqueTag = [set stringForColumn:@"uniqueTag"];
    reportTrouble.maintenanceTypeId = YWMaintenanceTypeTrouble;
    reportTrouble.troubleSequence = [set stringForColumn:@"troubleSequence"];
    if (reportTrouble.troubleSequence.length == 0) {
        reportTrouble.troubleSequence = @"无";
    }
    reportTrouble.qrcodeId = [set stringForColumn:@"qrcodeId"];
    
    NSInteger equipmentTypeId = [set intForColumn:@"equipmentTypeId"];
    NSString* sql = @"SELECT * FROM 'dict_equipment_type' where equipmentTypeId=?;";
    FMResultSet* newSet = [db executeQuery:sql, @(equipmentTypeId)];
    if ([newSet next]) {
        reportTrouble.equipmentType = [[YWDBTool shareDBTool] equipmentTypeWithFMResultSet:newSet db:db];
    }
    
    NSInteger locationId = [set intForColumn:@"locationId"];
    sql = @"SELECT * FROM 'location' where locationId=?;";
    newSet = [db executeQuery:sql, @(locationId)];
    if ([newSet next]) {
        reportTrouble.location = [[YWDBTool shareDBTool] locationWithFMResultSet:newSet];
    }
    
    NSString * buildingId = [set stringForColumn:@"buildingId"];
    sql = @"SELECT * FROM 'building' where buildingId=?;";
    newSet = [db executeQuery:sql, buildingId];
    if ([newSet next]) {
        reportTrouble.building = [[YWDBTool shareDBTool] buildingWithSet:newSet];
    }
    
    NSInteger apartmentId = [set intForColumn:@"apartmentId"];
    sql = @"SELECT * FROM 'apartment' where apartmentId=?;";
    newSet = [db executeQuery:sql, @(apartmentId)];
    if ([newSet next]) {
        reportTrouble.apartment = [[YWDBTool shareDBTool] apartmentWithFMResultSet:newSet];
    }
    
    NSInteger floorId = [set intForColumn:@"floorId"];
    sql = @"SELECT * FROM 'floor' where floorId=?;";
    newSet = [db executeQuery:sql, @(floorId)];
    if ([newSet next]) {
        reportTrouble.floor = [[YWDBTool shareDBTool] floorWithFMResultSet:newSet];
    }
    
    NSInteger reporterId = [set intForColumn:@"reporterId"];
    NSString* authorSql = @"SELECT * FROM 'handler' WHERE uniqueId =?;";
    FMResultSet* authorSet = [db executeQuery:authorSql, @(reporterId)];
    if ([authorSet next]) {
        reportTrouble.reporter = [[YWDBTool shareDBTool] handlerWithSet:authorSet];
    }
    reportTrouble.troubleStatus = [set intForColumn:@"troubleStatus"];
    reportTrouble.reportTime = [set longLongIntForColumn:@"reportTime"];
    reportTrouble.note = [set stringForColumn:@"note"];
    reportTrouble.cancelNote = [set stringForColumn:@"cancelNote"];
    reportTrouble.channel = [set intForColumn:@"channel"];
    
    NSString* equipmentId = [set stringForColumn:@"equipmentId"];
    NSString* equSql = @"SELECT * FROM 'equipment' WHERE equipmentId = ?;";
    FMResultSet* equSet = [db executeQuery:equSql,equipmentId];
    if ([equSet next]) {
        reportTrouble.equipment = [[YWDBTool shareDBTool] equipmentWithFMResultSet:equSet];
    }
    
    if (reportTrouble.channel == YWPostTroubleTypeWeChat || reportTrouble.channel == YWPostTroubleTypeWebSite) {
        NSString *weSql = @"SELECT * FROM 'weChatPostTroubleReporter' WHERE troubleId =?;";
        FMResultSet* weSet = [db executeQuery:weSql, @(reportTrouble.troubleId)];
        if ([weSet next]) {
            YWTroubleContact *troubleContact = [[YWTroubleContact alloc] init];
            troubleContact.troubleId = reportTrouble.troubleId;
            troubleContact.contactName = [weSet stringForColumn:@"contactName"];
            troubleContact.contactNo = [weSet stringForColumn:@"contactNo"];
            reportTrouble.troubleContact = troubleContact;
        }
    }
    reportTrouble.read = [set boolForColumn:@"read"];
    reportTrouble.uploadFail = [set boolForColumn:@"uploadFail"];
    reportTrouble.observerIds = [set stringForColumn:@"observerIds"];
    reportTrouble.systemTypeId = [set intForColumn:@"systemTypeId"];
    //报障系统类别
    NSString * typeSql = @"SELECT * FROM 'dict_equipmentSystemType_type' WHERE systemTypeId =?;";
    FMResultSet* typeSet = [db executeQuery:typeSql, @(reportTrouble.systemTypeId)];
    if ([typeSet next]) {
        YWEquipmentSystemType* equipmentSystemType = [[YWEquipmentSystemType alloc]init];
        equipmentSystemType.systemTypeId = [typeSet intForColumn:@"systemTypeId"];
        equipmentSystemType.systemTypeName = [typeSet stringForColumn:@"systemTypeName"];
        reportTrouble.equipmentSystemType = equipmentSystemType;
    }
    
    //观察者
    NSMutableArray *observers = [NSMutableArray array];
    NSString *observerSql = [NSString stringWithFormat:@"SELECT * FROM 'handler' WHERE uniqueId in (%@)",reportTrouble.observerIds];
    FMResultSet *observerSet = [db executeQuery:observerSql];
    while ([observerSet next]) {
        YWHandler *handler = [[YWDBTool shareDBTool] handlerWithSet:observerSet];
        [observers addObject:handler];
    }
    reportTrouble.observers = observers;
    
    if (need) {
        YWTroubleMissionModel *troubleMissionModel = nil;
        NSString* missionSql = @"SELECT * FROM 'YW_TroubleMission' WHERE troubleId = ?";
        FMResultSet* missionSet = [db executeQuery:missionSql, @(reportTrouble.troubleId)];
        if ([missionSet next]) {
            troubleMissionModel = [[YWTroubleMissionDBManager shareManager] troubleMissionModelWithFMResultSet:missionSet FMDatabase:db needSelectReportTrouble:YES];
        }
        reportTrouble.troubleMission = troubleMissionModel;
        if (troubleMissionModel == nil) {
            YWTroubleMissionModel *troubleMission = [[YWTroubleMissionModel alloc] init];
            //troubleMission.trouble = reportTrouble;
            troubleMission.trouble = reportTrouble;
            troubleMission.troubleStatus = reportTrouble.troubleStatus;
            reportTrouble.troubleMission = troubleMission;
        }
    }
    return reportTrouble;
}

- (NSArray*)uploadFailedReportTroubleArray{
    __block NSMutableArray* uploadFailReportTroubleArray = [NSMutableArray array];
    [self.dbQeue inDatabase:^(FMDatabase* db) {
        NSString* sql = nil;
        sql = [NSString stringWithFormat:@"SELECT * FROM 'YW_ReportTrouble' WHERE uploadFail = 1 AND troubleStatus=%lu",(unsigned long)YWTroubleMissionStatusUnAssign];
        FMResultSet* newset = [db executeQuery:sql];
        while ([newset next]) {
           YWReportTroubleModel* reportTrouble = [self reportTroubleWithFMResultSet:newset FMDatabase:db needSelectTroubleMission:YES];
            [uploadFailReportTroubleArray addObject:reportTrouble];
        }
        [db closeOpenResultSets];
    }];
    return uploadFailReportTroubleArray.count?uploadFailReportTroubleArray:nil;
}

@end
